package it.unitn.science.matr155863_148346_145963.bccstore;


import it.unitn.science.matr155863_148346_145963.bccstore.items.Category;
import it.unitn.science.matr155863_148346_145963.bccstore.items.Commission;
import it.unitn.science.matr155863_148346_145963.bccstore.items.Image;
import it.unitn.science.matr155863_148346_145963.bccstore.items.User;
import it.unitn.science.matr155863_148346_145963.bccstore.items.Product;
import it.unitn.science.matr155863_148346_145963.bccstore.items.UM;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DBManager{
    
  private final String DB_DRIVER = "com.mysql.jdbc.Driver";
  private Connection con=null;

  
  public DBManager(String dbUrl,String dbuser,String dbpsw){
    try{
          Class.forName(DB_DRIVER);
          con=DriverManager.getConnection(dbUrl,dbuser,dbpsw);
    }catch(Exception ex){
          throw new RuntimeException(ex);
    }
  }
    
    public void close() throws SQLException {
        if(!con.isClosed()){
            con.close();
        }
    }
    
    public User authenticate(String username,String password){
        User user=null;
        try{
            PreparedStatement pst=con.prepareStatement("SELECT * FROM user WHERE "
                    + User.USERNAME+"=? AND "
                    + User.PASSWORD+"=?");
            pst.setString(1, username);
            pst.setString(2, password);
            ResultSet rs=pst.executeQuery();
            if(rs.next()){
                user=new User(rs.getString(User.EMAIL),
                        rs.getString(User.USERNAME),
                        null,//Password
                        rs.getString(User.FIRST_NAME),
                        rs.getString(User.LAST_NAME),
                        rs.getDate(User.BIRTHDATE).getTime(),
                        rs.getBoolean(User.SELLER),
                        rs.getInt(User.ID)
                        );
            }
        }catch(Exception ex){
            throw new RuntimeException(ex);
        }
        return user;
    }

    public HashSet<Product> searchProducts(String name, Integer category, Float price,String sellerName,Integer sellerId) {
        HashSet<Product> products=new HashSet<Product>();
        try{
            PreparedStatement pst=prepareSearchStatement(name,category,price,sellerName,sellerId);
            int index=1;
            if(name!=null){
                pst.setString(index, name);
                index++;
            }
            if(category!=null){
                pst.setInt(index, category);
                index++;
            }
            if(price!=null){
                pst.setFloat(index, price);
                index++;
            }
            if(sellerName!=null){
                pst.setString(index, sellerName);
                index++;
            }
            if(sellerId!=null){
                pst.setInt(index, sellerId);
            }
            ResultSet rs=pst.executeQuery();
            while(rs.next()){
                Product product=new Product(
                      rs.getInt("_id_product"),
                      rs.getString("name"),
                      rs.getFloat("price"),
                      rs.getInt("quantity"),
                      rs.getInt("um_id"),
                      rs.getString("um_name"),
                      rs.getInt("image_id"),
                      rs.getString("image_name"),
                      rs.getString("description"),
                      rs.getInt("seller_id"),
                      rs.getInt("category_id"),
                      rs.getString("seller_name"),
                      rs.getString("category_name")
                      );
                products.add(product);
            }
        }catch(Exception ex){
            throw new RuntimeException(ex);
        }        
        return products;
    }

    private PreparedStatement prepareSearchStatement(String name, Integer category, Float price,String sellerName,Integer sellerId) 
            throws SQLException {
        String query="SELECT P._id_product, P.name, P.price, P.quantity, P.um AS um_id, "
                + "um.name AS um_name, "
                + "I._id_image AS image_id,"
                + "I.name AS image_name,"
                + "P.description,"
                + "P.seller AS seller_id,"
                + "P.category AS category_id,"
                + " username AS seller_name,"
                + "C.name AS category_name "
                //+ "(first_name || ' ' || last_name ) AS seller_name"
                + " FROM user, product P, category C, um, image I "
                + " WHERE P.category=_id_category AND _id_user=P.seller"
                + " AND P.um=_id_um AND P.image=I._id_image AND ";
        if(name!=null){
            query=query+"name=? AND ";
        }
        if(category!=null){
            query=query+"P.category=? AND ";
        }
        if(price!=null){
            query=query+"price<? AND ";
        }
        if(sellerName!=null){
            //query=query+"seller= (SELECT _id_user FROM user WHERE first_name || ' ' || last_name like ? )  AND ";
            query=query+"P.seller= (SELECT _id_user FROM user WHERE username like ? )  AND ";
        }
        if(sellerId!=null){
            query=query+"P.seller=?     ";
        }
        query=query.substring(0,query.length()-5);
        return con.prepareStatement(query);
    }
    
    public Product getProduct(Integer productId){
        Product product=null;
        try{
            PreparedStatement pst=con.prepareStatement("SELECT "
                    + "P._id_product, "
                    + "P.name, "
                    + "P.price, "
                    + "P.quantity, "
                    + "P.um AS um_id, "
                    + "um.name AS um_name, "
                    + "I._id_image AS image_id,"
                    + "I.name AS image_name,"
                    + "P.description,"
                    + "P.seller AS seller_id,"
                    + "P.category AS category_id,"
                    + " username AS seller_name,"
                    + "C.name AS category_name "
                    + " FROM product P, user , category C, image I, um"
                    + " WHERE _id_product=? AND P.image=I._id_image "
                    + "AND P.seller=_id_user AND P.category=_id_category AND"
                    + " P.um=_id_um" );
            pst.setInt(1, productId);
            ResultSet rs=pst.executeQuery();
            if(rs.next()){
              product=new Product(
                      rs.getInt("_id_product"),
                      rs.getString("name"),
                      rs.getFloat("price"),
                      rs.getInt("quantity"),
                      rs.getInt("um_id"),
                      rs.getString("um_name"),
                      rs.getInt("image_id"),
                      rs.getString("image_name"),
                      rs.getString("description"),
                      rs.getInt("seller_id"),
                      rs.getInt("category_id"),
                      rs.getString("seller_name"),
                      rs.getString("category_name")
                      );
            }
        }catch(Exception ex){
            throw new RuntimeException(ex);
        }
        return product;
    }

    public int getProductId(String trackName,Integer authorId){
        Integer trackId=null;
        try{
            PreparedStatement pst=con.prepareStatement("SELECT _id_product FROM product "
                    + "WHERE name=? AND seller=?" );
            pst.setString(1, trackName);
            pst.setInt(2, authorId);
            ResultSet rs=pst.executeQuery();
            if(rs.next()){
                trackId=rs.getInt("_id_product");
            }
        }catch(Exception ex){
            throw new RuntimeException(ex);
        }
        return trackId;
    }

    public Integer addProduct(Product product) {
        Integer productId=null;
        try{
            //con.startTransuction()
            con.setAutoCommit(false);
            //Devo aggiornare la tabella, ho camibato il campo name
            PreparedStatement pst=con.prepareStatement("INSERT INTO product"
                    + "(name, price,quantity,um ,image ,description ,seller ,category )"
                  + " VALUES (?,?,?,?,?,?,?,?);",PreparedStatement.RETURN_GENERATED_KEYS);
            pst.setString(1, product.getName());
            pst.setFloat(2, product.getPrice());
            pst.setInt(3, product.getQuantity());
            pst.setInt(4, product.getUmId());
            pst.setInt(5, product.getImageId());
            pst.setString(6, product.getDescription());
            pst.setInt(7, product.getSellerId());
            pst.setInt(8, product.getCategoryId());
            pst.executeUpdate();
            ResultSet rs = pst.getGeneratedKeys();
            if (rs != null){
                try{
                    if (rs.next()) {
                        productId=rs.getInt(1);
                    }
                } finally {
                        rs.close();
                }
            }
            con.commit();
            //=getProductId(product.getName(), product.getSellerId());
        }catch(SQLException ex){
            String message=ex.toString();
            if(ex.getErrorCode()==1062){
                message="There is already a product with this name, change it.";
            }
            try{
                con.rollback();
            } catch (SQLException ex1) {
                message=ex1.toString();
            }
            throw new RuntimeException(message);
        }finally{
            try{
                if (!con.getAutoCommit()) {
                    con.setAutoCommit(true);
                }
            }catch(Exception ex){
                throw new RuntimeException("Error diuring autocommit");
            }
        }
        return productId;
    }
    
    public String categoryExist(Integer category) {
        String name=null;
        try {
            PreparedStatement pst=con.prepareStatement(
                            "SELECT name"
                    + " FROM category WHERE _id_category=?");
            pst.setInt(1, category);
            ResultSet rs=pst.executeQuery();
            if(rs.next()){
                name=rs.getString("name");
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        return name;
    }
    
    public ArrayList<Category> getCategories() {
        ArrayList<Category> categories=new ArrayList<Category>();
        try {
            PreparedStatement pst=con.prepareStatement(
                            "SELECT * FROM category ");
            ResultSet rs=pst.executeQuery();
            while(rs.next()){
                Category cat=new Category(rs.getInt("_id_category"),
                        rs.getString("name"));
                categories.add(cat);
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        return categories;
    }
    
    public String umExist(Integer um) {
        String name=null;
        try {
            PreparedStatement pst=con.prepareStatement(
                            "SELECT name"
                    + " FROM um WHERE _id_um=?");
            pst.setInt(1, um);
            ResultSet rs=pst.executeQuery();
            if(rs.next()){
                name=rs.getString("name");
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        return name;
    }
    
    public ArrayList<UM> getUMs() {
        ArrayList<UM> ums=new ArrayList<UM>();
        try {
            PreparedStatement pst=con.prepareStatement(
                            "SELECT * FROM um ");
            ResultSet rs=pst.executeQuery();
            while(rs.next()){
                UM um=new UM(rs.getInt("_id_um"),
                        rs.getString("name"));
                ums.add(um);
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        return ums;
    }
    
    public String sellerExist(Integer seller) {
        String name=null;
        try {
            PreparedStatement pst=con.prepareStatement(
                            "SELECT (first_name|| ' ' || last_name) AS name"
                    + " FROM user WHERE _id_user=?");
            pst.setInt(1, seller);
            ResultSet rs=pst.executeQuery();
            if(rs.next()){
                name=rs.getString("name");
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        return name;
    }

    public ArrayList<Image> getImages() {
        ArrayList<Image> images=new ArrayList<Image>();
        try {
            PreparedStatement pst=con.prepareStatement(
                            "SELECT * FROM image ");
            ResultSet rs=pst.executeQuery();
            while(rs.next()){
                Image image=new Image(rs.getInt("_id_image"),rs.getString("name"));
                images.add(image);
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        return images;
    }

    public Image getImage(int imageId) {
        Image image=null;
        try {
            PreparedStatement pst=con.prepareStatement(
                            "SELECT * FROM image WHERE _id_image=?");
            pst.setInt(1, imageId);
            ResultSet rs=pst.executeQuery();
            while(rs.next()){
                image=new Image(rs.getInt("_id_image"),rs.getString("name"));
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        return image;
    }

    public Product setQuantityProduct(int quantita,int idInt) {
         try {
            PreparedStatement pst=con.prepareStatement(
                            "UPDATE product "
                          + "SET quantity=? "
                          + "WHERE _id_product=? ");
            pst.setInt(1, quantita);
            pst.setInt(2, idInt);
            pst.executeUpdate();
            return getProduct(idInt);
            /*while(rs.next()){
                image=new Image(rs.getInt("_id_image"),rs.getString("name"));
            }*/
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
    }

    public User getSellerOf(int idProd) {
        User user=null;
        try{
            PreparedStatement pst=con.prepareStatement("SELECT * FROM user WHERE "
                    + "_id_user=(SELECT seller FROM product WHERE _id_product=?)");
            pst.setInt(1, idProd);
            ResultSet rs=pst.executeQuery();
            if(rs.next()){
                user=new User(rs.getString(User.EMAIL),
                        rs.getString(User.USERNAME),
                        null,//Password
                        rs.getString(User.FIRST_NAME),
                        rs.getString(User.LAST_NAME),
                        rs.getDate(User.BIRTHDATE).getTime(),
                        rs.getBoolean(User.SELLER),
                        rs.getInt(User.ID)
                        );
            }
        }catch(Exception ex){
            throw new RuntimeException(ex);
        }
        return user;
    }

    public int addCommission(Commission commission) {
        Integer idCommission=null;
        try{
            con.setAutoCommit(false);
            PreparedStatement pst=con.prepareStatement("INSERT INTO commission"
                    + "(buyer, seller,product,quantity ,price,um ,bill,date)"
                  + " VALUES (?,?,?,?,?,?,?,?);",PreparedStatement.RETURN_GENERATED_KEYS);
            pst.setInt(1, commission.getBuyerId());
            pst.setInt(2, commission.getSellerId());
            pst.setInt(3, commission.getProductId());
            pst.setInt(4, commission.getQuantity());
            pst.setFloat(5, commission.getPrice());
            pst.setInt(6, commission.getUmId());
            pst.setString(7, commission.getBill());
            pst.setDate(8, new java.sql.Date(commission.getDate().getTime()));
            pst.executeUpdate();
            ResultSet rs = pst.getGeneratedKeys();
            if (rs != null){
                try{
                    if (rs.next()) {
                        idCommission=rs.getInt(1);
                    }
                } finally {
                        rs.close();
                }
            }
            con.commit();
            //=getProductId(product.getName(), product.getSellerId());
        }catch(SQLException ex){
            String message=ex.toString();
            if(ex.getErrorCode()==1062){
                message="There is already a bill with this name, change it.";
            }
            try{
                con.rollback();
            } catch (SQLException ex1) {
                message=ex1.toString();
            }
            throw new RuntimeException(message);
        }finally{
            try{
                if (!con.getAutoCommit()) {
                    con.setAutoCommit(true);
                }
            }catch(Exception ex){
                throw new RuntimeException("Error diuring autocommit");
            }
        }
        return idCommission;
    }

    private Commission getCommission(String bill)  {
        Commission commission=null;
        try{
        PreparedStatement pst=con.prepareStatement(
                "SELECT com._id_commission, com.buyer, com.seller,"
                    +" usBuy.first_name AS buyer_first_name,"
                    +" usBuy.last_name AS buyer_last_name,"
                    +" usBuy.username AS buyer_username," 
                    +" usSel.first_name AS seller_first_name,"
                    +" usSel.last_name AS seller_last_name,"
                    +" usSel.username AS seller_username," 
                    +" com.product,"
                    +" com.quantity,"
                    +" com.price,"
                    +" com.bill,"
                    +" com.um AS um_id,"
                    +" umTab.name AS um_name,"
                    +" com.date "
                    +" FROM ( commission AS com JOIN user AS usBuy ON com.buyer=usBuy._id_user )"
                    +" JOIN user AS usSel ON com.seller=usSel._id_user"
                        +" JOIN um AS umTab ON com.um=umTab._id_um "
                    +   " WHERE com.bill=?;"
                );
        pst.setString(1, bill);
        ResultSet rs=pst.executeQuery();
        if(rs.next()){
        commission=new Commission( 
                            rs.getInt("_id_commission"), 
                            rs.getInt("buyer"), 
                            rs.getInt("seller"),
                            rs.getString("buyer_first_name"),  
                            rs.getString("buyer_last_name"),
                            rs.getString("buyer_username"), 
                            rs.getString("seller_first_name"),
                            rs.getString("seller_last_name"),
                            rs.getString("seller_username"),
                            rs.getInt("product"),//productId
                            null, //product
                            rs.getInt("quantity"),
                            rs.getFloat("price"),
                            rs.getInt("um_id"),
                            rs.getString("um_name"),
                            rs.getString("bill"),
                            rs.getDate("date").getTime());
        Product prod=getProduct(commission.getProductId());
        commission.setProduct(prod);
        }
        }catch(SQLException ex){
            throw new RuntimeException(ex);
        }
        return commission;
    }
    
    public ArrayList<Commission> getAllUserCommission(Integer idUser)  {
        ArrayList<Commission> commissions=new ArrayList<Commission>();
        try{
            PreparedStatement pst=con.prepareStatement(
                    "SELECT com._id_commission, com.buyer, com.seller," 
                    +" usBuy.first_name AS buyer_first_name,"
                    +" usBuy.last_name AS buyer_last_name,"
                    +" usBuy.username AS buyer_username," 
                    +" usSel.first_name AS seller_first_name,"
                    +" usSel.last_name AS seller_last_name,"
                    +" usSel.username AS seller_username,"
                    +" com.product,"
                    +" com.quantity,"
                    +" com.price,"
                    + "com.um,"
                    + "umis.name,"
                    +" com.bill,"
                    + "com.date," 
                    +" pr.name AS product_name"
                    +" FROM ( commission AS com JOIN user AS usBuy ON com.buyer=usBuy._id_user )"
                    + "JOIN um AS umis ON com.um = umis._id_um"   //da mic
                    +" JOIN user AS usSel ON com.seller=usSel._id_user"
                    +" JOIN product AS pr ON com.product=pr._id_product"
                    +   " WHERE usBuy._id_user=? "
                    + ";"
                    
             );
            
        pst.setInt(1, idUser);
        ResultSet rs=pst.executeQuery();
        while(rs.next()){
            Commission commission=new Commission( 
                            rs.getInt("_id_commission"), 
                            rs.getInt("buyer"), 
                            rs.getInt("seller"),
                            rs.getString("buyer_first_name"),  
                            rs.getString("buyer_last_name"),
                            rs.getString("buyer_username"), 
                            rs.getString("seller_first_name"),  
                            rs.getString("seller_last_name"),
                            rs.getString("seller_username"),
                            rs.getInt("product"),//productId
                            null, //product
                            rs.getInt("quantity"),
                            rs.getFloat("price"),
                            rs.getInt("um"),   // o um_id??
                            rs.getString("name"),
                            rs.getString("bill"),
                            rs.getDate("date").getTime());
        Product prod=getProduct(commission.getProductId());
        commission.setProduct(prod);
        commissions.add(commission);
        }
        }catch(SQLException ex){
            throw new RuntimeException(ex);
        }
        return commissions;
    }

    public ArrayList<Commission> getAllSellCommission(Integer idSeller) {
        ArrayList<Commission> commissions=new ArrayList<Commission>();
        try{
            PreparedStatement pst=con.prepareStatement(
                    "SELECT com._id_commission, com.buyer, com.seller," 
                    +" usBuy.first_name AS buyer_first_name,"
                    +" usBuy.last_name AS buyer_last_name,"
                    +" usBuy.username AS buyer_username," 
                    +" usSel.first_name AS seller_first_name,"
                    +" usSel.last_name AS seller_last_name,"
                    +" usSel.username AS seller_username,"
                    +" com.product,"
                    +" com.quantity,"
                    +" com.price,"
                    + "com.um,"
                    + "umis.name,"
                    +" com.bill,"
                    + "com.date," 
                    +" pr.name AS product_name"
                    +" FROM ( commission AS com JOIN user AS usBuy ON com.buyer=usBuy._id_user )"
                    + "JOIN um AS umis ON com.um = umis._id_um"   //da mic
                    +" JOIN user AS usSel ON com.seller=usSel._id_user"
                    +" JOIN product AS pr ON com.product=pr._id_product"
                    +   " WHERE usSel._id_user=? "
                    + ";"
                    
             );
            
        pst.setInt(1, idSeller);
        ResultSet rs=pst.executeQuery();
        while(rs.next()){
            Commission commission=new Commission( 
                            rs.getInt("_id_commission"), 
                            rs.getInt("buyer"), 
                            rs.getInt("seller"),
                            rs.getString("buyer_first_name"),  
                            rs.getString("buyer_last_name"),
                            rs.getString("buyer_username"), 
                            rs.getString("seller_first_name"),  
                            rs.getString("seller_last_name"),
                            rs.getString("seller_username"),
                            rs.getInt("product"),//productId
                            null, //product
                            rs.getInt("quantity"),
                            rs.getFloat("price"),
                            rs.getInt("um"),   // o um_id??
                            rs.getString("name"),
                            rs.getString("bill"),
                            rs.getDate("date").getTime());
        Product prod=getProduct(commission.getProductId());
        commission.setProduct(prod);
        commissions.add(commission);
        }
        }catch(SQLException ex){
            throw new RuntimeException(ex);
        }
        return commissions;
    }


}